package com.hclc.util.poi;

import com.hclc.util.condition.AssembleUtil;
import com.hclc.util.date.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Iterator;
import java.util.List;
import java.util.function.Supplier;

@Slf4j
public class ExcelUtil {
    public static final String PATTERN_COMMUNITY_NAME = "^[\\u4E00-\\u9FA5A-Za-z0-9]+$";// 小区名|楼栋名|单元
    public static final String PATTERN_HOUSES_NAME = "^[\\u4E00-\\u9FA5A-Za-z0-9#]+$";// 房屋名

    /**
     * 导出excel
     *
     * @param headerName （excel列名称）
     * @param headerKey  （导出对象属性名）
     * @param sheetName  （excel 页签名称）
     * @param dataList   （导出的数据）
     * @return
     */
    public static HSSFWorkbook createExcel(String[] headerName, String[] headerKey, String sheetName, List dataList) {
        try {
            if (headerKey.length <= 0) {
                return null;
            }
            if (dataList.size() <= 0) {
                return null;
            }
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet;
            if ((sheetName == null) || ("".equals(sheetName))) {
                sheet = wb.createSheet("Sheet1");
            } else {
                sheet = wb.createSheet(sheetName);
            }
            HSSFRow row = sheet.createRow(0);
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.LEFT);
            HSSFCell cell = null;
            if (headerName.length > 0) {
                for (int i = 0; i < headerName.length; i++) {
                    cell = row.createCell(i);
                    cell.setCellValue(headerName[i]);
                    cell.setCellStyle(style);

                }
            }
            int n = 0;
            HSSFCellStyle contextStyle = wb.createCellStyle();
            contextStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00_);(#,##0.00)"));

            HSSFCellStyle contextStyle1 = wb.createCellStyle();
            HSSFDataFormat format = wb.createDataFormat();
            contextStyle1.setDataFormat(format.getFormat("@"));


            HSSFCell cell0 = null;
            HSSFCell cell1 = null;

            for (Iterator localIterator = dataList.iterator(); localIterator.hasNext(); ) {
                Object obj = localIterator.next();
                Field[] fields = obj.getClass().getDeclaredFields();
                row = sheet.createRow(n + 1);
                for (int j = 0; j < headerKey.length; j++) {
                    if (headerName.length <= 0) {
                        cell0 = row.createCell(j);
                        cell0.setCellValue(headerKey[j]);
                        cell0.setCellStyle(style);

                    }
                    for (int i = 0; i < fields.length; i++) {
                        if (fields[i].getName().equals(headerKey[j])) {
                            fields[i].setAccessible(true);
                            if (fields[i].get(obj) == null) {
                                row.createCell(j).setCellValue("");
                                break;
                            }
                            if ((fields[i].get(obj) instanceof Number)) {//如果为数字
                                cell1 = row.createCell(j);
                                cell1.setCellType(CellType.NUMERIC);
                                cell1.setCellStyle(contextStyle);
                                cell1.setCellValue(fields[i].get(obj).toString());
                                break;
                            }
                            if ("".equals(fields[i].get(obj))) {//如果为空
                                cell1 = row.createCell(j);
                                cell1.setCellStyle(contextStyle);
                                row.createCell(j).setCellValue("");
                                cell1.setCellType(CellType.STRING);
                                break;
                            }
                            if ((fields[i].get(obj) instanceof String)) {
                                cell1 = row.createCell(j);
                                cell1.setCellStyle(contextStyle1);
                                cell1.setCellType(CellType.STRING);
                                cell1.setCellValue(fields[i].get(obj).toString());
                                break;
                            }

                        }

                    }
                }
                n++;
            }
            for (int i = 0; i < headerKey.length; i++) {
                sheet.setColumnWidth(i, headerKey[i].getBytes().length * 2 * 256);
            }
            HSSFWorkbook localHSSFWorkbook1 = wb;
            return localHSSFWorkbook1;
        } catch (Exception e) {
            log.error("在创建excel时发生异常", e);
            return null;
        } finally {
        }
    }

    public static Object parseCellAttribute(Cell cellVal, ExcelCellTypeEnums excelCellTypeEnums, Supplier<String> supplier) {
        int rowIndex = cellVal.getRowIndex() + 1;
        int columnIndex = cellVal.getColumnIndex() + 1;
        StringBuilder stringBuilder = new StringBuilder();
        AssembleUtil.build().isTrueThenConsumer(!cellVal.getCellType().equals(CellType.STRING), (s) -> {
            stringBuilder.append("  错误单元格坐标：");
            stringBuilder.append(getIndexLabel(columnIndex) + rowIndex);
            throw new RuntimeException("单元格必须为字符串" + stringBuilder.toString());
        });

        String stringCellValue = cellVal.getStringCellValue();
        AssembleUtil.build().isTrueThenConsumer(!StringUtils.isNotBlank(stringCellValue), (s) -> {
            stringBuilder.append("  错误单元格坐标：");
            stringBuilder.append(getIndexLabel(columnIndex) + rowIndex);
            throw new RuntimeException(supplier.get() + stringBuilder.toString());
        });

        if (ExcelCellTypeEnums.TYPE_STRING.equals(excelCellTypeEnums)){
            boolean matches = stringCellValue.matches(PATTERN_COMMUNITY_NAME);
            AssembleUtil.build().isTrueThenConsumer(!matches, (s) -> {
                stringBuilder.append("  错误单元格坐标：");
                stringBuilder.append(getIndexLabel(columnIndex) + rowIndex);
                throw new RuntimeException(supplier.get() + stringBuilder.toString());
            });
        }

        try {
            switch (excelCellTypeEnums) {
                case TYPE_LONG:
                    return Long.parseLong(stringCellValue);
                case TYPE_DOUBLE:
                    return Double.parseDouble(stringCellValue);
                case TYPE_INTEGER:
                    return Integer.parseInt(stringCellValue);
                case TYPE_STRING:
                    return stringCellValue;
                case TYPE_BIG_DECIMAL:
                    return new BigDecimal(stringCellValue);
                case TYPE_LOCAL_DATE:
                    return DateUtil.parseLocalDateByStr(stringCellValue);
                case TYPE_LOCAL_DATE_TIME:
                    return LocalDateTime.of(DateUtil.parseLocalDateByStr(stringCellValue), LocalTime.MIN);
                default:
            }
        } catch (Exception e) {
            log.error("Excel转换异常,rowIndex：{},columnIndex：{}", rowIndex, columnIndex, e);
            throw new RuntimeException("Excel读取发生错误,请确认类型是否准确，错误单元格坐标：" + rowIndex + "行," + columnIndex + "列");
        }
        throw new RuntimeException("Excel读取发生错误。");
    }

    /**
     * 字母数组
     */
    private static String[] sources = new String[]{"A", "B", "C", "D", "E",
            "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
            "S", "T", "U", "V", "W", "X", "Y", "Z"};

    /**
     * (256 for *.xls, 16384 for *.xlsx)
     *
     * @param columnNum 列的个数，从1开始
     * @return 返回[1, columnNum]共columnNum个对应xls列字母的数组
     * @throws IllegalArgumentException 如果 columnNum 超出该范围 [1,16384]
     */
    public static String[] getColumnLabels(int columnNum) {
        if (columnNum < 1 || columnNum > 16384) {
            throw new IllegalArgumentException();
        }
        String[] columns = new String[columnNum];
        if (columnNum < 27) {
            System.arraycopy(sources, 0, columns, 0, columnNum);
            return columns;
        }
        int multiple = -1;
        int remainder;
        System.arraycopy(sources, 0, columns, 0, 26);
        int currentLoopIdx = 0;
        if (columnNum < 703) {
            for (int i = 26; i < columnNum; i++) {
                remainder = currentLoopIdx % 26;
                if (remainder == 0) {
                    multiple++;
                }
                columns[i] = sources[multiple] + columns[remainder];
                currentLoopIdx++;
            }
        } else {
            int currentLen = 26;
            int totalLen = 26;
            int lastLen = 0;
            for (int i = 26; i < columnNum; i++) {
                remainder = currentLoopIdx % currentLen;
                if (remainder == 0) {
                    multiple++;
                    int j = multiple % 26;
                    if (j == 0 && multiple != 0) {
                        lastLen = totalLen;
                        currentLen = 26 * currentLen;
                        totalLen = currentLen + lastLen;
                        currentLoopIdx = 0;
                    }
                }
                columns[i] = sources[multiple % 26]
                        + columns[remainder + lastLen];
                currentLoopIdx++;
            }
        }

        return columns;
    }

    /**
     * 返回该列号对应的字母
     *
     * @param columnNo (xls的)第几列（从1开始）
     */
    private static String getCorrespondingLabel(int columnNo) {
        if (columnNo < 1/** ||columnNo>16384 **/
        ) {
            throw new IllegalArgumentException();
        }

        StringBuilder sb = new StringBuilder(5);
        int remainder = columnNo % 26;
        if (remainder == 0) {
            sb.append("Z");
            remainder = 26;
        } else {
            sb.append(sources[remainder - 1]);
        }

        while ((columnNo = (columnNo - remainder) / 26 - 1) > -1) {
            remainder = columnNo % 26;
            sb.append(sources[remainder]);
        }

        return sb.reverse().toString();
    }

    /**
     * 列号转字母
     *
     * @param columnIndex poi里xls的列号（从0开始）
     * @return 该列对应的字母
     * @throws IllegalArgumentException if columnIndex less than 0
     */
    public static String getIndexLabel(int columnIndex) {
        return getCorrespondingLabel(columnIndex + 1);
    }

}
